探索DuckDB：DuckDB 让 SQL 更友好
还没有看DuckDB 0.10.0 发布了，带来了重大更新，别错过了。
原文：Even Friendlier SQL with DuckDB[1]
翻译：Gemini Pro
校对：alitrack
日期：2023 年 8 月 23 日
作者：亚历克斯·莫纳汉
DuckDB 让 SQL 更加友好
重点提示：DuckDB 继续突破 SQL 语法的界限，既简化查询，又使更高级的分析成为可能。亮点包括动态列选择、以 FROM 子句开头的查询、函数链接和列表解析。我们大胆地去往以前没有 SQL 引擎去过的地方！
谁说 SQL 应该停留在时间长河中，被束缚在 1999 年版本的规范中？作为对比，大家还记得在 Promises 出现之前 JavaScript 是什么样子的吗？直到 2012 年它才发布！很明显，在编程语法层面的创新可以对整个语言生态系统产生深远而积极的影响。
我们认为，SQL 语言的创新有很多正当理由，其中包括简化基本查询以及使更动态的分析成为可能的机会。其中许多功能都源于社区的建议！请在 Discord[2] 或 GitHub[3] 上告诉我们您在使用 SQL 时遇到的痛点，并加入我们，共同改变编写 SQL 的感受！
如果您还没有机会阅读本系列的第一篇文章，请快速浏览一下 此处[4]。
未来就在当下
此列表中的前几个增强功能包含在上一篇文章的“未来构想”部分中。
可重用的列别名
在 select 语句中使用增量计算表达式时，传统的 SQL 方言会强制您为每列写出完整的表达式，或围绕计算的每一步创建一个公共表表达式 (CTE)。现在，任何列别名都可以被同一 select 语句中的后续列重用。不仅如此，这些别名还可以在 where 和 order by 子句中使用。
旧方法 1：重复自己
select
'These are the voyages of the starship Enterprise...' AS intro,
instr('These are the voyages of the starship Enterprise...', 'starship') AS starship_loc
substr('These are the voyages of the starship Enterprise...', instr('These are the voyages of the starship Enterprise...', 'starship') + len('starship') + 1) AS trimmed_intro;
旧方法 2：所有 CTE
WITH intro_cte AS (
SELECT
'These are the voyages of the starship Enterprise...' AS intro
), starship_loc_cte AS (
SELECT
intro,
instr(intro, 'starship') AS starship_loc
FROM intro_cte
)
SELECT
intro,
starship_loc,
substr(intro, starship_loc + len('starship') + 1) AS trimmed_intro
FROM starship_loc_cte;
新方法
SELECT
'These are the voyages of the starship Enterprise...' AS intro,
instr(intro, 'starship') AS starship_loc,
substr(intro, starship_loc + len('starship') + 1) AS trimmed_intro;
| intro | starship_loc | trimmed_intro |
| These are the voyages of the starship Enterprise… | 30 | Enterprise… |
动态列选择
数据库通常更喜欢列定义的严格性和行数的灵活性。这可以通过强制执行数据类型和记录列级别元数据来提供帮助。然而，在数据科学工作流和其他地方，动态生成列（例如在特征工程期间）非常常见。
您不再需要预先知道所有列名！DuckDB 可以根据正则表达式模式匹配、EXCLUDE 或 REPLACE 修饰符，甚至 lambda 函数（有关详细信息，请参阅下面的 lambda 函数部分！）来选择甚至修改列。
让我们来看一些关于第一季星际迷航收集的事实。使用 DuckDB 的 httpfs 扩展[5]，我们可以直接从 GitHub 查询 csv 数据集。它有几列，所以让我们用 DESCRIBE 来描述它。
INSTALL httpfs;
LOAD httpfs;
CREATE TABLE trek_facts AS
SELECT * FROM 'https://raw.githubusercontent.com/Alex-Monahan/example_datasets/main/Star_Trek-Season_1.csv';
DESCRIBE trek_facts;
| column_name | column_type | null | key | default | extra |
| season_num | BIGINT | YES | NULL | NULL | NULL |
| episode_num | BIGINT | YES | NULL | NULL | NULL |
| aired_date | DATE | YES | NULL | NULL | NULL |
| cnt_kirk_hookups | BIGINT | YES | NULL | NULL | NULL |
| cnt_downed_redshirts | BIGINT | YES | NULL | NULL | NULL |
| bool_aliens_almost_took_over_planet | BIGINT | YES | NULL | NULL | NULL |
| bool_aliens_almost_took_over_enterprise | BIGINT | YES | NULL | NULL | NULL |
| cnt_vulcan_nerve_pinch | BIGINT | YES | NULL | NULL | NULL |
| cnt_warp_speed_orders | BIGINT | YES | NULL | NULL | NULL |
| highest_warp_speed_issued | BIGINT | YES | NULL | NULL | NULL |
| bool_hand_phasers_fired | BIGINT | YES | NULL | NULL | NULL |
| bool_ship_phasers_fired | BIGINT | YES | NULL | NULL | NULL |
| bool_ship_photon_torpedos_fired | BIGINT | YES | NULL | NULL | NULL |
| cnt_transporter_pax | BIGINT | YES | NULL | NULL | NULL |
| cnt_damn_it_jim_quote | BIGINT | YES | NULL | NULL | NULL |
| cnt_im_givin_her_all_shes_got_quote | BIGINT | YES | NULL | NULL | NULL |
| cnt_highly_illogical_quote | BIGINT | YES | NULL | NULL | NULL |
| bool_enterprise_saved_the_day | BIGINT | YES | NULL | NULL | NULL |
带有正则表达式的 COLUMNS()
COLUMNS 表达式可以接受一个字符串参数，该参数是一个正则表达式，并将返回所有与该模式匹配的列名。曲速在第一季中是如何变化的？让我们检查任何包含单词 warp 的列名。
SELECT
episode_num,
COLUMNS('.*warp.*')
FROM trek_facts;
| episode_num | cnt_warp_speed_orders | highest_warp_speed_issued |
| 0 | 1 | 1 |
| 1 | 0 | 0 |
| 2 | 1 | 1 |
| 3 | 1 | 0 |
| … | … | … |
| 27 | 1 | 1 |
| 28 | 0 | 0 |
| 29 | 2 | 8 |
COLUMNS 表达式也可以被其他函数包装，以便将这些函数应用于每个选定的列。让我们简化上面的查询，以查看所有剧集中的最大值：
SELECT
MAX(COLUMNS('.*warp.*'))
FROM trek_facts;
| max(trek_facts.cnt_warp_speed_orders) | max(trek_facts.highest_warp_speed_issued) |
| 5 | 8 |
我们还可以创建一个 WHERE 子句，该子句适用于多列。所有列都必须符合筛选条件，这相当于将它们与 AND 结合起来。哪些剧集的曲速命令至少为 2，曲速等级至少为 2？
SELECT
episode_num,
COLUMNS('.*warp.*')
FROM trek_facts
WHERE
COLUMNS('.*warp.*') >= 2;
-- cnt_warp_speed_orders >= 2
-- AND
-- highest_warp_speed_issued >= 2各个列在计算前也可以被排除或替换。例如，由于我们的数据集只包含第一季，我们不需要查找该列的 MAX。这非常不合理。
SELECT
MAX(COLUMNS(* EXCLUDE season_num))
FROM trek_facts;
| max(trek_facts.episode_num) | max(trek_facts.aired_date) | max(trek_facts.cnt_kirk_hookups) | … | max(trek_facts.bool_enterprise_saved_the_day) |
| 29 | 1967-04-13 | 2 | … | 1 |
当应用于一组动态列时，REPLACE 语法也很有用。在此示例中，我们希望在查找每列中的最大值之前将日期转换为时间戳。以前，这需要一个完整的子查询或 CTE 来预处理该单列！
SELECT
MAX(COLUMNS(* REPLACE aired_date::timestamp AS aired_date))
FROM trek_facts;
| max(trek_facts.season_num) | max(trek_facts.episode_num) | max(aired_date := CAST(aired_date AS TIMESTAMP)) | … | max(trek_facts.bool_enterprise_saved_the_day) |
| 1 | 29 | 1967-04-13 00:00:00 | … | 1 |
COLUMNS() 与 lambda 函数
查询一组动态列的最灵活的方法是通过 lambda 函数[6]。这允许将任何匹配条件应用于列的名称，而不仅仅是正则表达式。有关 lambda 函数的更多详细信息，请参见下文。
例如，如果使用 LIKE 语法更舒适，我们可以选择与 LIKE 模式匹配的列，而不是使用正则表达式。
SELECT
episode_num,
COLUMNS(col -> col LIKE '%warp%')
FROM trek_facts
WHERE
COLUMNS(col -> col LIKE '%warp%') >= 2;
| episode_num | cnt_warp_speed_orders | highest_warp_speed_issued |
| 14 | 3 | 7 |
| 17 | 2 | 7 |
| 18 | 2 | 8 |
| 29 | 2 | 8 |
自动将 JSON 转换为嵌套类型
该系列的第一部分提到 JSON 点符号引用作为未来的工作。然而，该团队已经走得更远了！现在，JSON 可以 自动解析[7] 为 DuckDB 的原生类型，以实现更快的性能、压缩以及友好的点符号！
首先，如果它们没有与您正在使用的客户端捆绑在一起，请安装并加载 httpfs 和 json 扩展。然后直接查询远程 JSON 文件，就像它是表一样！
INSTALL httpfs;
LOAD httpfs;
INSTALL json;
LOAD json;
SELECT
starfleet[10].model AS starship
FROM 'https://raw.githubusercontent.com/vlad-saling/star-trek-ipsum/master/src/content/content.json';
| starship |
| USS Farragut - NCC-1647 - Ship on which James Kirk served as a phaser station operator. Attacked by the Dikironium Cloud Creature, killing half the crew. ad. |
现在，让我们来看看一些超越前一篇文章中想法的新 SQL 功能！
在 SELECT 语句中首先使用 FROM
在构建查询时，您需要知道的第一件事是您的数据来自哪里 FROM。那么为什么这是 SELECT 语句中的第二个子句呢？不再是了！DuckDB 正在构建 SQL，因为它本应一直如此 - 将 FROM 子句放在第一位。这解决了关于 SQL 最长久以来的抱怨之一，DuckDB 团队在 2 天内实现了它。
FROM my_table SELECT my_column;
不仅如此，SELECT 语句可以完全删除，DuckDB 将假定应该 SELECT 所有列。现在查看表就像这样简单：
FROM my_table;
-- SELECT * FROM my_table
COPY 等其他语句也得到了简化。
COPY (FROM trek_facts) TO 'phaser_filled_facts.parquet';
除了节省击键和保持开发流程状态之外，这还有另一个好处：当您开始选择要查询的列时，自动完成将具有更多上下文。给 AI 一个帮助之手！
请注意，此语法是完全可选的，因此您的 SELECT * FROM 键盘快捷键是安全的，即使它们已经过时了……🙂
函数链接
许多 SQL 博客建议使用 CTE 而不是子查询。除其他好处外，它们更具可读性。操作被划分成离散的块，并且可以从上到下阅读，而不是强迫读者从里到外工作。
DuckDB 为每个标量函数启用了相同的可解释性改进！使用点运算符将函数链接在一起，就像在 Python 中一样。链中的前一个表达式用作后续函数的第一个参数。
SELECT
('Make it so')
.UPPER()
.string_split(' ')
.list_aggr('string_agg','.')
.concat('.') AS im_not_messing_around_number_one;
| im_not_messing_around_number_one |
| MAKE.IT.SO. |
现在将它与旧方法进行比较…
SELECT
concat(
list_aggr(
string_split(
UPPER('Make it stop'),
' '),
'string_agg','.'),
'.') AS oof;
| oof |
| MAKE.IT.STOP. |
按名称联合
DuckDB 旨在融合数据库和数据框的优点。这种新语法受到 Pandas 中的 concat 函数[8] 的启发。不是根据列位置垂直堆叠表，而是按名称匹配列并相应地堆叠。只需将 UNION 替换为 UNION BY NAME 或将 UNION ALL 替换为 UNION ALL BY NAME。
例如，我们不得不在下一代中添加一些新的外星谚语：
CREATE TABLE proverbs AS
SELECT
'Revenge is a dish best served cold' AS klingon_proverb
UNION ALL BY NAME
SELECT
'You will be assimilated' AS borg_proverb,
'If winning is not important, why keep score?' AS klingon_proverb;
FROM proverbs;
| klingon_proverb | borg_proverb |
| Revenge is a dish best served cold | NULL |
| If winning is not important, why keep score? | You will be assimilated |
这种方法还有其他好处。如上所示，不仅可以组合列顺序不同的表，还可以组合列数完全不同的表。这在模式迁移时很有用，对于 DuckDB 的 多文件读取功能[9] 特别有用。
按名称插入
在 SQL 中列顺序严格的另一个常见情况是将数据插入表时。列必须完全匹配顺序，或者必须在查询中的两个位置重复所有列名。
相反，在插入时在表名后添加关键字 BY NAME。可以按任何顺序插入表中任何列的任何子集。
INSERT INTO proverbs BY NAME
SELECT 'Resistance is futile' AS borg_proverb;
SELECT * FROM proverbs;
| klingon_proverb | borg_proverb |
| Revenge is a dish best served cold | NULL |
| If winning is not important, why keep score? | You will be assimilated |
| NULL | Resistance is futile |
动态透视和取消透视
从历史上看，数据库不适合透视操作。但是，DuckDB 的 PIVOT 和 UNPIVOT 子句可以创建或堆叠动态列名，以实现真正灵活的透视功能！除了这种灵活性之外，DuckDB 还提供了 SQL 标准语法和更友好的简写。
例如，让我们来看看地球-罗慕伦战争开始时的采购预测数据：
| 项目 | 年份 | 数量 |
| 相位器 | 2155 | 1035 |
| 相位器 | 2156 | 25039 |
| 相位器 | 2157 | 95000 |
| 光子鱼雷 | 2155 | 255 |
| 光子鱼雷 | 2156 | 17899 |
| 光子鱼雷 | 2157 | 87492 |
如果将每年的数据放在一起，则更容易比较我们的相位器需求和光子鱼雷需求。让我们将其转换为更友好的格式！每一年都应该有自己的列（但查询中不需要指定每一年！），我们希望对总count求和，并且我们仍然希望为每个item保留一个单独的组（行）。
CREATE TABLE pivoted_purchases AS
PIVOT purchases
ON year
USING SUM(count)
GROUP BY item;
FROM pivoted_purchases;
| 项目 | 2155 | 2156 | 2157 |
| 相位器 | 1035 | 25039 | 95000 |
| 光子鱼雷 | 255 | 17899 | 87492 |
看起来光子鱼雷正在促销……
现在想象一下相反的情况。工程部的斯科蒂一直在直观地分析并手动构建他的采购预测。他更喜欢以透视表的形式，这样更容易阅读。现在你需要把它放回数据库中！这场战争可能会持续一段时间，所以你可能需要在明年再次这样做。让我们编写一个UNPIVOT查询以返回可以处理任何年份的原始格式。
COLUMNS表达式将使用除item之外的所有列。堆叠后，包含pivoted_purchases中列名的列应重命名为year，这些列中的值表示count。结果与原始数据集相同。
UNPIVOT pivoted_purchases
ON COLUMNS(* EXCLUDE item)
INTO
NAME year
VALUE count;
| 项目 | 年份 | 数量 |
| 相位器 | 2155 | 1035 |
| 相位器 | 2156 | 25039 |
| 相位器 | 2157 | 95000 |
| 光子鱼雷 | 2155 | 255 |
| 光子鱼雷 | 2156 | 17899 |
| 光子鱼雷 | 2157 | 87492 |
更多示例包含在我们DuckDB 0.8.0 公告[10]中，PIVOT[11]和UNPIVOT[12]文档页面重点介绍了更复杂的查询。
敬请期待未来的帖子，以了解幕后发生的事情！
列出 lambda 函数
列表 lambda 允许对列表中的每个项目应用操作。这些不需要预先定义 - 它们是在查询中即时创建的。
在此示例中，lambda 函数与list_transform函数结合使用，以缩短每个官方船名。
SELECT
(['Enterprise NCC-1701', 'Voyager NCC-74656', 'Discovery NCC-1031'])
.list_transform(x -> x.string_split(' ')[1]) AS short_name;
| ship_name |
| [Enterprise, Voyager, Discovery] |
Lambda 还可以用于过滤列表中的项目。lambda 返回一个布尔值列表，list_filter函数使用该列表来选择特定项目。contains函数使用前面描述的函数链接。
SELECT
(['Enterprise NCC-1701', 'Voyager NCC-74656', 'Discovery NCC-1031'])
.list_filter(x -> x.contains('1701')) AS the_original;
| the_original |
| [Enterprise NCC-1701] |
列表解析
如果有一种简单的语法可以同时修改和过滤列表，那会怎么样？DuckDB 从 Python 的列表解析方法中汲取灵感，极大地简化了上述示例。列表解析是语法糖 - 这些查询在幕后被重写为 lambda 表达式！
在括号中，首先指定所需的转换，然后指示应迭代哪个列表，最后包括筛选条件。
SELECT
[x.string_split(' ')[1]
FOR x IN ['Enterprise NCC-1701', 'Voyager NCC-74656', 'Discovery NCC-1031']
IF x.contains('1701')] AS ready_to_boldly_go;
| ready_to_boldly_go |
| [Enterprise] |
展开 struct.*
DuckDB 中的结构是一组键/值对。在幕后，结构存储在为每个键单独列中。因此，将结构分解为单独的列在计算上很容易，现在在语法上也很简单！这是允许 SQL 处理动态列名的另一个示例。
WITH damage_report AS (
SELECT {'gold_casualties':5, 'blue_casualties':15, 'red_casualties': 10000} AS casualties
)
FROM damage_report
SELECT
casualties.*;
| gold_casualties | blue_casualties | red_casualties |
| 5 | 15 | 10000 |
自动创建结构
DuckDB 提供了一种简单的方法将任何表转换为单列结构。不是SELECT列名，而是SELECT表名本身。
WITH officers AS (
SELECT 'Captain' AS rank, 'Jean-Luc Picard' AS name
UNION ALL
SELECT 'Lieutenant Commander', 'Data'
)
FROM officers
SELECT officers;
| officers |
| {‘rank’: Captain, ‘name’: Jean-Luc Picard} |
| {‘rank’: Lieutenant Commander, ‘name’: Data} |
联合数据类型
DuckDB 利用强类型来提供高性能并确保数据质量。但是，DuckDB 也尽可能宽容，使用隐式转换等方法来避免总是必须在数据类型之间进行转换。
DuckDB 启用灵活性的另一种方法是新的UNION数据类型。UNION数据类型允许单个列包含多种类型的值。这可以被认为是 SQLite 的灵活数据类型规则的“选择加入”（与 SQLite 最近宣布的严格表[13]相反）。
默认情况下，DuckDB 在将表组合在一起时会寻找数据类型的公分母。以下查询生成VARCHAR列：
SELECT 'The Motion Picture' AS movie UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 'First Contact';
| movie |
| varchar |
| The Motion Picture |
| First Contact |
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
但是，如果使用UNION类型，则每行都保留其原始数据类型。UNION使用键值对定义，其中键作为名称，值作为数据类型。这也允许将特定数据类型提取为单独的列：
CREATE TABLE movies (
movie UNION(num INT, name VARCHAR)
);
INSERT INTO movies
VALUES ('The Motion Picture'), (2), (3), (4), (5), (6), ('First Contact');
FROM movies
SELECT
movie,
union_tag(movie) AS type,
movie.name,
movie.num;
| movie | type | name | num |
| union(num integer, name varchar) | varchar | varchar | int32 |
| The Motion Picture | name | The Motion Picture | |
| 2 | num | 2 | |
| 3 | num | 3 | |
| 4 | num | 4 | |
| 5 | num | 5 | |
| 6 | num | 6 | |
| First Contact | name | First Contact |
其他友好功能
值得一提的其他几个友好功能，其中一些功能强大到足以保证有自己的博客文章。DuckDB 借鉴了 Pandas 中的 describe 函数[14]，并实现了一个 SUMMARIZE 关键字，该关键字将计算数据集中的每一列的各种统计信息，以便快速、高级地概述。只需将 SUMMARIZE 添加到任何表或 SELECT 语句前即可。
请参阅 相关子查询文章[15] 以了解如何使用引用彼此列的子查询。DuckDB 的高级优化器将相关子查询的性能提高了几个数量级，从而允许尽可能自然地表达查询。曾经由于性能原因而成为反模式的东西现在可以自由使用了！
DuckDB 添加了更多将表连接在一起的 JOIN 方式，这使得表达常见计算变得更加容易。一些像 LATERAL、ASOF、SEMI 和 ANTI 连接出现在其他系统中，但在 DuckDB 中具有高性能实现。DuckDB 还添加了一个新的 POSITIONAL 连接，它通过每张表中的行号进行组合，以匹配 Pandas 常用的按行号索引进行连接的功能。有关详细信息，请参阅 JOIN 文档[16]，并留意一篇描述 DuckDB 最先进的 ASOF 连接的博文！
总结和未来工作
DuckDB 旨在成为最易于使用的数据库。成为进程内、零依赖和强类型化的基本架构决策有助于实现这一目标，但其 SQL 方言的友好性也产生了很大的影响。通过扩展行业标准的 PostgreSQL 方言，DuckDB 旨在提供表达所需数据转换的最简单方法。这些更改包括从 SELECT 语句的古老子句顺序开始，以 FROM 开始，允许使用函数进行链式处理的基本新方式，以及高级嵌套数据类型计算（如列表解析）。这些功能均可在 0.8.1 版本中使用。
未来更友好的 SQL 工作包括：
• 具有多个参数的 Lambda 函数，例如
list_zip• 下划线作为数字分隔符（例如：1_000_000 而不是 1000000）
• 扩展用户体验，包括自动加载
• 改进文件通配符
• 您的建议！
引用链接
[1] Even Friendlier SQL with DuckDB: https://duckdb.org/2023/08/23/even-friendlier-sql.html[2] Discord: https://discord.duckdb.org/[3] GitHub: https://github.com/duckdb/duckdb/discussions[4] 此处: https://duckdb.org/2022/05/04/friendlier-sql.html[5] httpfs 扩展: https://duckdb.org/docs/extensions/httpfs[6] lambda 函数: https://duckdb.org/docs/sql/functions/nested#lambda-functions[7] 自动解析: https://duckdb.org/2023/03/03/json.html[8] Pandas 中的 concat 函数: https://pandas.pydata.org/docs/reference/api/pandas.concat.html[9] 多文件读取功能: https://duckdb.org/docs/data/multiple_files/combining_schemas#union-by-name[10] DuckDB 0.8.0 公告: https://duckdb.org/2023/05/17/announcing-duckdb-080.html#new-sql-features[11] PIVOT: https://duckdb.org/docs/sql/statements/pivot[12] UNPIVOT: https://duckdb.org/docs/sql/statements/unpivot[13] 严格表: https://www.sqlite.org/stricttables.html[14] describe 函数: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.describe.html[15] 相关子查询文章: https://duckdb.org/2023/05/26/correlated-subqueries-in-sql.html[16] JOIN 文档: https://duckdb.org/docs/sql/query_syntax/from.html
更多DuckDB文章，请搜索在本公众号搜索：duckdb